/*
 	Script: GearsORM.Sql.js
 	        Contains functions to generate sql
 	
 	License:
 	        MIT-style license.
*/

GearsORM.Sql =
{
	deleteRowByIdAndTable:function(tableName)
	{
		return ["DELETE FROM ",tableName," WHERE rowid = ?"].join("");
	},
	dropTableByName:function(tableName)
	{
		return "DROP TABLE IF EXISTS " + tableName;
	},
	createTableByModelClass:function(modelClass)
	{
		var options = modelClass.options;
		var fields = options.fields;
		var query = ["CREATE TABLE IF NOT EXISTS  ",options.name,"("];
		for(var fieldName in fields)
		{
			var field = fields[fieldName];
			//ignore ManyToOne and ManyToMany,as they are backward relations
			if(!field.isBackwardRelation)
			{
				if(!field.isRelation)
				{
					query.push(fieldName);
					query.push(" ");
				}
				query.push(field.toSql());
				query.push(",");
			}
		}
		query = query.splice(0,query.length-1);
		query.push(")");
		return query.join("");
	},
	deleteFromTableWhere:function(tableName,whereExpression)
	{
		return ["DELETE FROM ",tableName,whereExpression ? " WHERE " : "",whereExpression].join("");	
	},
	updateByModel:function(modelClass)
	{
		return ["UPDATE ",modelClass.options.name," SET ",GearsORM.Sql._fieldsValue(modelClass)," WHERE rowid = ?"].join("");
	},
	selectCount:function(tableName,whereExpression)
	{
		return ["SELECT COUNT(*) as c FROM ",tableName,whereExpression ? " WHERE " + whereExpression :""].join("");
	},
	selectWithManyToMany:function(modelClass,relatedClass,m2mTableName,whereExpression,rowid)
	{
		var query = ["SELECT DISTINCT "];
		query.push(GearsORM.Sql._selectFields(relatedClass));
		query.push(" FROM ");
		query.push(relatedClass.options.name);
		query.push(GearsORM.Sql._selectJoins(relatedClass));
		query.push(GearsORM.Sql._m2mJoins(modelClass.options.name,relatedClass.options.name,m2mTableName,rowid));
		if(whereExpression)
		{
			query.push(" WHERE ");
			query.push(whereExpression);
		}
		return query.join("");
	},
	selectWithForigenKeys:function(modelClass,whereExpression)
	{
		var query = ["SELECT "];
		query.push(GearsORM.Sql._selectFields(modelClass));
		query.push(" FROM ");
		query.push(modelClass.options.name);
		query.push(GearsORM.Sql._selectJoins(modelClass));
		if(whereExpression)
		{
			query.push(" WHERE ");
			query.push(whereExpression);
		}
		return query.join("");
	},
	insertRowByModel:function(modelClass)
	{
		var self = GearsORM.Sql;
		return ["INSERT INTO ",modelClass.options.name," (",
		self._filedsByModel(modelClass),
		") VALUES (",
		self._valuesByModel(modelClass),")"].join("");
	},	
	oneToManySql:function(relatedModelName,allowNull)
	{
		return [relatedModelName,"_id INTEGER",!allowNull ? " NOT NULL " : " " ,"CONSTRAINT fk_",relatedModelName,"_id REFERENCES ",relatedModelName,"(rowid)"].join("");
	},
	createInsertForigenKeyTrigger:function(tableName,relatedTableName,allowNull)
	{
		return GearsORM.Sql._createInsertUpdateForigenKeyTrigger(tableName,relatedTableName,false,allowNull);
	},
	createUpdateForigenKeyTrigger:function(tableName,relatedTableName,allowNull)
	{
		return GearsORM.Sql._createInsertUpdateForigenKeyTrigger(tableName,relatedTableName,true,allowNull);
	},
	createDeleteRestrictTrigger:function(tableName,relatedTableName)
	{
		return GearsORM.Sql._createDeleteCascadeOrRestrictTrigger(tableName,relatedTableName,false);
	},
	createDeleteCascadeTrigger:function(tableName,relatedTableName)
	{
		return GearsORM.Sql._createDeleteCascadeOrRestrictTrigger(tableName,relatedTableName,true);
	},
	dropForigenKeyInsertTrigger:function(tableName,relatedTableName)
	{
		return GearsORM.Sql._dropForigenKeyTrigger(tableName,relatedTableName,"i");
	},
	dropForigenKeyUpdateTrigger:function(tableName,relatedTableName)
	{
		return GearsORM.Sql._dropForigenKeyTrigger(tableName,relatedTableName,"u");
	},
	dropForigenKeyDeleteTrigger:function(tableName,relatedTableName)
	{
		return GearsORM.Sql._dropForigenKeyTrigger(tableName,relatedTableName,"d");
	},
	_createDeleteCascadeOrRestrictTrigger:function(tableName,relatedTableName,cascade)
	{
		
		  var sql = ["CREATE TRIGGER IF NOT EXISTS fkd_",tableName,"_",relatedTableName,"_rowid ",
		  			"BEFORE DELETE ON ",relatedTableName,
		  			" FOR EACH ROW BEGIN"];
		  if(cascade)
		  {
		  	sql.push(" DELETE from ");
			sql.push(tableName);
			sql.push(" WHERE ");
			sql.push(relatedTableName);
			sql.push("_id = OLD.rowid;");		
		  }
		  else
		  {
		  	sql.push(" SELECT RAISE(ROLLBACK, 'delete on table \"");
			sql.push(relatedTableName);
			sql.push("\" violates foreign key constraint \"fk_");
			sql.push(tableName);
			sql.push("_");
			sql.push(relatedTableName);
			sql.push("_id\"')");
			sql.push("WHERE (SELECT ");
			sql.push(relatedTableName);
			sql.push("_id FROM ");
			sql.push(tableName);
			sql.push(" WHERE ");
			sql.push(relatedTableName);
		    sql.push("_id = OLD.rowid) IS NOT NULL;");
		  }
		  sql.push(" END;");
		  return sql.join("");
	},
	_createInsertUpdateForigenKeyTrigger:function(tableName,relatedTableName,update,allowNull)
	{
		var sql =  ["CREATE TRIGGER IF NOT EXISTS fk",update ? "u" : "i","_",tableName,"_",relatedTableName,"_rowid BEFORE "];
		if(update)
			sql.push("UPDATE");
		else
			sql.push("INSERT");
			
		sql.push(" ON ");
		sql.push(tableName);
		sql.push(" FOR EACH ROW BEGIN ");
		sql.push("SELECT RAISE(ROLLBACK, '");
		if(update)
			sql.push("update");
		else
			sql.push("insert");
		sql.push(" on table \"");
		sql.push(tableName);
		sql.push("\" violates foreign key constraint \"fk_");
		sql.push(relatedTableName);
		sql.push("_rowid");
		sql.push("\"') WHERE ");
		if(allowNull)
		{
			sql.push("NEW.");
			sql.push(relatedTableName);
			sql.push("_id IS NOT NULL AND ");
		}
		sql.push("(SELECT rowid FROM ");
		sql.push(relatedTableName);
		sql.push(" WHERE rowid = NEW.");
		sql.push(relatedTableName);
		sql.push("_id) IS NULL; END;");
		return sql.join("");
	},
	_selectFields:function(modelClass)
	{
		var fields = modelClass.options.fields;
		var modelName = modelClass.options.name;
		var sql = [modelName,".rowid AS ",modelName,"_rowid,"];
		for(var fieldName in fields)
		{
			var field = fields[fieldName];
			if(!field.isBackwardRelation)
			{
				if(field.isRelation)
				{
					var relatedClass = field.getRelatedClass();
					if(relatedClass == modelClass)
					{
						sql.push(modelName);
						sql.push(".");
						sql.push(relatedClass.options.name);
						sql.push("_id");
					}
					else
						sql.push(GearsORM.Sql._selectFields(relatedClass));
				}
				else
				{
					sql.push(modelName);
					sql.push(".");
					sql.push(fieldName);
					sql.push(" AS ");
					sql.push(modelName);
					sql.push("_");
					sql.push(fieldName);
				}
				sql.push(",");
			}
		}
		return sql.splice(0,sql.length-1).join("");
	},
	_selectJoins:function(modelClass)
	{
		var fields = modelClass.options.fields;
		var name = modelClass.options.name;
		var sql = [];
		for(var fieldName in fields)
		{
			var field = fields[fieldName];
			if(field.isRelation && !field.isBackwardRelation && field.getRelatedClass() != modelClass)
			{
				var relatedClass = field.getRelatedClass();
				var relatedName = relatedClass.options.name;
				sql.push(" LEFT JOIN ");
				sql.push(relatedName);
				sql.push(" ON ");
				sql.push(relatedName);
				sql.push(".rowid = ");
				sql.push(name);
				sql.push(".");
				sql.push(relatedName);
				sql.push("_id ");
				sql.push(GearsORM.Sql._selectJoins(relatedClass));
			}
		}
		return sql.join("");
	},
	_m2mJoins:function(modelName,relatedName,m2mTableName,rowid)
	{
		return [" JOIN ",m2mTableName," ON ",m2mTableName,".",relatedName,"_id = ",relatedName,".rowid AND ",m2mTableName,".",modelName,"_id =",rowid].join("");
	},
	_filedsByModel:function(modelClass)
	{
		var fields = [];
		for(var fieldName in modelClass.options.fields)
		{
			var field = modelClass.options.fields[fieldName];
			if(!field.isBackwardRelation)
			{
				if(!field.isRelation)
					fields.push(fieldName);
				else
				{
					fields.push(field.getRelatedClass().options.name+"_id");
				}	
			}
		}
		return fields.join(",");
	},
	_valuesByModel:function(modelClass)
	{
		var values = [];
		var fields = modelClass.options.fields;
		for(var fieldName in fields)
		{
			if(!fields[fieldName].isBackwardRelation)values.push("?");
		}
		return values.join(",");
	},
	_fieldsValue:function(modelClass)
	{
		var fields = modelClass.options.fields;
		var sql = [];
		for(var fieldName in fields)
		{
			var field = fields[fieldName];
			if(!field.isBackwardRelation)
			{
				if(!field.isRelation)
				{
					sql.push(fieldName+"=?");
				}
				else
				{
					sql.push(field.getRelatedClass().options.name+"_id"+"=?");
				}
			}
		}
		return sql.join(",");
	},
	_dropForigenKeyTrigger:function(tableName,relatedTableName,type)
	{
		return ["DROP TRIGGER IF EXISTS ","fk",type,"_",tableName,"_",relatedTableName,"_rowid"].join("");
	}
};